<?php
//**************************************************
// all query string should be quoted by double quote
//**************************************************

/*
	return db resource or false
*/
function connGet(){
	global $DBADD, $DBUSER, $DBPASS, $DBNAME;
	
	$conn = mysql_pconnect($DBADD, $DBUSER, $DBPASS);
	if ($conn == false){
		return false;
	}
	
	if (mysql_select_db($DBNAME, $conn) == false){
		return false;
	}
	
	return $conn;
}

function connClose($conn){
	mysql_close($conn);
}

/*
	userinfo contains all user related information (hash map)
	return: false, user id, dup error
*/
function addUser($userinfo){
	global $EMAILDUP_ERROR, $USERINFO_TB_NAME;
	
	$result = array();
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	// check duplicated email
	$query = "select * from ".$USERINFO_TB_NAME." where company_email = ".procStrField($userinfo['company_email']);		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		if (mysql_num_rows($resource) == 1){
			return $EMAILDUP_ERROR;
		}
	}		
	
	$query = "insert into ".$USERINFO_TB_NAME." (contact_name,company_name,hst_number,company_address,company_city,company_province,company_postal_code,company_phone_number,company_fax_number,company_email,Password) values(".
		procStrField($userinfo['contact_name']).",".
		procStrField($userinfo['company_name']).",".
		procStrField($userinfo['hst_number']).",".
		procStrField($userinfo['company_address']).",".
		procStrField($userinfo['company_city']).",".
		procStrField($userinfo['company_province']).",".
		procStrField($userinfo['company_postal_code']).",".
		procStrField($userinfo['company_phone_number']).",".
		procStrField($userinfo['company_fax_number']).",".
		procStrField($userinfo['company_email']).",".
		procStrField($userinfo['password']).")";
	
	$resource = mysql_query($query,$conn);

	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		$query = "select LAST_INSERT_ID(ID) as ID from ".$USERINFO_TB_NAME." order by ID desc limit 1";		
		$resource = mysql_query($query,$conn);
		if ($resource == false){
			connClose($conn);
			return false;
		}
		else {
			$result = mysql_fetch_assoc($resource);
			connClose($conn);
			// return user id
			return $result['ID'];
		}
	}
}

/*
	return false or user hash
*/
function getUserById($id){
	global $USERINFO_TB_NAME;

	$result = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$USERINFO_TB_NAME." where ID = ".$id;
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		$result = mysql_fetch_assoc($resource);
		connClose($conn);
		return $result;
	}
}

/*
	return false or user hash
*/
function getUserByEmail($email){
	global $USERINFO_TB_NAME;

	$result = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$USERINFO_TB_NAME." where company_email = ".procStrField($email);
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		$result = mysql_fetch_assoc($resource);
		connClose($conn);
		return $result;
	}
}

/*
	userinfo contains all user related information (hash map)
	return: false, true
*/
function updateUserById($id, $userinfo){
	global $USERINFO_TB_NAME;
	
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "update ".$USERINFO_TB_NAME." set ".
		"ContactName =".procStrField($userinfo['ContactName']).",".
		"DoctorName =".procStrField($userinfo['DoctorName']).",".
		"CompanyName =".procStrField($userinfo['CompanyName']).",".
		"FullAddress =".procStrField($userinfo['FullAddress']).",".
		"City =".procStrField($userinfo['City']).",".
		"Province =".procStrField($userinfo['Province']).",".
		"PostalCode =".procStrField($userinfo['PostalCode']).",".
		//"PhoneNumber =".procStrField($userinfo['PhoneNumber']).",".
		"FaxNumber =".procStrField($userinfo['FaxNumber']).",".
		"Email =".procStrField($userinfo['Email']).",".
		"MethodPayment =".procStrField($userinfo['MethodPayment']).",".
		"Password =".procStrField($userinfo['Password']).",".
		"Block =".$userinfo['Block'].
		" where ID = ".$id;

	$resource = mysql_query($query, $conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		connClose($conn);
		return true;
	}
}

/*
	add item to cart
	cart item has following structure
	
	category id, product id, unit price, quantity (no duplicated item in cart)
	return: true, false
*/
function addUpdateCart($catid, $prodid, $uprice, $qty, $prodname){
	if (isset($_SESSION['cart']) == false){
		// add net new
		$_SESSION['cart'] = array();
		
		array_push($_SESSION['cart'], array($catid,$prodid,$uprice,$qty, $prodname));
	}
	else {
		// update existing
		$isExist = false;
		
		foreach ($_SESSION['cart'] as $key => $arr){
			// looking for category id and product id match
			if ($arr[0] == $catid && $arr[1] == $prodid){
				//$arr[0] = $catid;
				//$arr[1] = $prodid;
				//$arr[2] = $uprice;
				$arr[3] = $qty;
				//$arr[4] = $prodname;
				
				$_SESSION['cart'][$key] = $arr;
				$isExist = true;
			}
		}
		
		if ($isExist == false){
			array_push($_SESSION['cart'], array($catid,$prodid,$uprice,$qty,$prodname));
		}
	}
}

/*
	delete item from cart by category id and product id
*/
function delCart($catid, $prodid){
	foreach ($_SESSION['cart'] as $key => $arr){
		// looking for category id and product id match
		if ($arr[0] == $catid && $arr[1] == $prodid){
			unset($_SESSION['cart'][$key]);
		}
	}
}

/*
	return subtotal or false
*/
function cartItemSubtotal($catid, $prodid){
	$result = false;
	
	foreach ($_SESSION['cart'] as $arr){
		// looking for category id and product id match
		if ($arr[0] == $catid && $arr[1] == $prodid){
			$result = $arr[3] *	$arr[2];
		}
	}
	
	return mny_format($result);
}

/*
	return grand total or false
*/
function cartItemGrandTotal(){
	$result = false;
	
	foreach ($_SESSION['cart'] as $arr){
		$result += $arr[3] *	$arr[2];
	}
	
	return mny_format($result);
}

/*
	output shopping cart list
*/
function cartList(){
	if (isset($_SESSION['cart']) && count($_SESSION['cart']) != 0){
		// table begin
		print('<table>');

		// header
		print('<tr>');
		print('<td>');
		print('Product ID');
		print('</td>');
		print('<td>');
		print('Product Name');
		print('</td>');
		print('<td>');
		print('Quantity');
		print('</td>');
		print('<td>');
		print('Unit Price');
		print('</td>');
		print('<td>');
		print('Subtotal');
		print('</td>');
		print('<td>');
		print('Action');
		print('</td>');
		print('</tr>');		
	
		$count = 0;
		foreach ($_SESSION['cart'] as $arr){
			print('<tr>');
		
  		print('<input type=\'hidden\' name=\'catid'.$count.'\' id=\'catid'.$count.'\' value=\''.$arr[0].'\'/>');
  		print('<input type=\'hidden\' name=\'prodid'.$count.'\' id=\'prodid'.$count.'\' value=\''.$arr[1].'\'/>');

  		print('<td>');
  		print($arr[1]);
  		print('</td>');

  		print('<td>');
  		print($arr[4]);
  		print('</td>');
  
  		print('<td>');
  		print('<input type=\'text\' name=\'qty'.$count.'\' id=\'qty'.$count.'\' value=\''.$arr[3].'\' size=\'4\' maxlength=\'4\' onkeyup=\'stripChar(this);getTotal();\'/>');
  		print('</td>');
  		
  		print('<td>');
  		print('<span id=\'uprice'.$count.'\'>');
  		print($arr[2]);
  		print('</span>');
  		print('</td>');
  
  		print('<td>');
  		print('<span id=\'subtotal'.$count.'\'>');
  		print(cartItemSubtotal($arr[0],$arr[1]));
  		print('</span>');
  		print('</td>');
  
  		print('<td>');
  		print('<a href=\'cart.php?action=remove&catid='.$arr[0].'&prodid='.$arr[1].'\'>Remove</a>');
  		print('</td>');
  
  		print('</tr>');
  		
  		$count++;
  	}
  
		// empty line
		print('<tr>');
		print('<td>');
		print('');
		print('</td>');
		print('<td>');
		print('');
		print('</td>');
		print('<td>');
		print('');
		print('</td>');
		print('<td>');
		print('');
		print('</td>');
		print('<td>');
		print('');
		print('</td>');
		print('</tr>');

		// footer
		print('<tr>');
		print('<td>');
		print('');
		print('</td>');
		print('<td>');
		print('');
		print('</td>');
		print('<td>');
		print('Total');
		print('</td>');
		print('<td>');
		print('<span id=\'grandtotal\'>');
		print(cartItemGrandTotal());
		print('</span>');
		print('</td>');
		print('<td>');
		print('');
		print('</td>');
		print('</tr>');

  	// table end
  	print('</table>');
  	
  	// unlisted items section
  	// title
		print('Unlisted Items - please enter the product code and required quantity to receive
a quotation');
		print('<table>');
		print('<tr>');
		print('<td>Product Name</td>');
		print('<td>Quantity</td>');
		print('</tr>');
		print('<tr>');
		print('<td><input type=\'text\' name=\'unlistedProdCode\' id=\'unlistedProdCode\' value=\'\' size=\'10\' maxlength=\'10\'/></td>');
		print('<td><input type=\'text\' name=\'unlistedQuantity\' id=\'unlistedQuantity\' value=\'\' size=\'4\' maxlength=\'4\' onkeyup=\'stripChar(this);getTotal();\'/></td>');
		print('</tr>');
		print('</table>');
  	
		print('<input type=\'submit\' name=\'action\' value=\'Checkout\' onclick=\'return doCheckout()\'/>');
		print('&nbsp;');
		print('<input type=\'submit\' name=\'action\' value=\'Update\' onclick=\'return doUpdate()\'/>');
		print('&nbsp;');
		print('<input type=\'button\' name=\'toprod\' value=\'Continue Shopping\' onclick=\'toProd()\'/>');
	}
	else {
  	// unlisted items section
  	// title
		print('Unlisted Items - please enter the product code and required quantity to receive
a quotation');
		print('<table>');
		print('<tr>');
		print('<td>Product Name</td>');
		print('<td>Quantity</td>');
		print('</tr>');
		print('<tr>');
		print('<td><input type=\'text\' name=\'unlistedProdCode\' id=\'unlistedProdCode\' value=\'\' size=\'10\' maxlength=\'10\'/></td>');
		print('<td><input type=\'text\' name=\'unlistedQuantity\' id=\'unlistedQuantity\' value=\'\' size=\'4\' maxlength=\'4\' onkeyup=\'stripChar(this);getTotal();\'/></td>');
		print('</tr>');
		print('</table>');
  	
		print('<input type=\'submit\' name=\'action\' value=\'Checkout\' onclick=\'return doCheckout()\'/>');
		print('&nbsp;');
		print('<input type=\'submit\' name=\'action\' value=\'Update\' onclick=\'return doUpdate()\'/>');
		print('&nbsp;');
		print('<input type=\'button\' name=\'toprod\' value=\'Continue Shopping\' onclick=\'toProd()\'/>');

		//print('Shopping cart is empty.');
	}
}

/*
	output order list
*/
function orderList($prodCode, $quantity){
	if ($prodCode == ''){
		$prodCode = 'N/A';
	}
	
	if ($quantity == ''){
		$quantity = 'N/A';
	}
	
	$result = '';
	if (isset($_SESSION['cart']) && count($_SESSION['cart']) != 0){
		// table begin
		$result .= '<table>';

		// header
		$result .= '<tr>';
		$result .= '<td>';
		$result .= 'Product ID';
		$result .= '</td>';
		$result .= '<td>';
		$result .= 'Product Name';
		$result .= '</td>';
		$result .= '<td>';
		$result .= 'Quantity';
		$result .= '</td>';
		$result .= '<td>';
		$result .= 'Unit Price';
		$result .= '</td>';
		$result .= '<td>';
		$result .= 'Subtotal';
		$result .= '</td>';
		$result .= '</tr>';		
	
		foreach ($_SESSION['cart'] as $arr){
			$result .= '<tr>';
		
  		$result .= '<td>';
  		$result .= $arr[1];
  		$result .= '</td>';

  		$result .= '<td>';
  		$result .= $arr[4];
  		$result .= '</td>';
  
  		$result .= '<td>';
  		$result .= $arr[3];
  		$result .= '</td>';
  		
  		$result .= '<td>';
  		$result .= $arr[2];
  		$result .= '</td>';
  
  		$result .= '<td>';
  		$result .= cartItemSubtotal($arr[0],$arr[1]);
  		$result .= '</td>';
  
  		$result .= '</tr>';
  	}
  
  	// empty line
		$result .= '<tr>';
		$result .= '<td>';
		$result .= '';
		$result .= '</td>';
		$result .= '<td>';
		$result .= '';
		$result .= '</td>';
		$result .= '<td>';
		$result .= '';
		$result .= '</td>';
		$result .= '<td>';
		$result .= '';
		$result .= '</td>';
		$result .= '</tr>';

		// footer
		$result .= '<tr>';
		$result .= '<td>';
		$result .= '';
		$result .= '</td>';
		$result .= '<td>';
		$result .= '';
		$result .= '</td>';
		$result .= '<td>';
		$result .= 'Total';
		$result .= '</td>';
		$result .= '<td>';
		$result .= cartItemGrandTotal();
		$result .= '</td>';
		$result .= '</tr>';

  	// table end
  	$result .= '</table>';
  	
  	// unlisted items section
  	// title
		$result .= 'Unlisted Items - please enter the product code and required quantity to receive
a quotation';
		$result .= '<table>';
		$result .= '<tr>';
		$result .= '<td>Product Name</td>';
		$result .= '<td>Quantity</td>';
		$result .= '</tr>';
		$result .= '<tr>';
		$result .= '<td>'.$prodCode.'</td>';
		$result .= '<td>'.$quantity.'</td>';
		$result .= '</tr>';
		$result .= '</table>';
	}
	else {
  	// unlisted items section
  	// title
		$result .= 'Unlisted Items - please enter the product code and required quantity to receive
a quotation';
		$result .= '<table>';
		$result .= '<tr>';
		$result .= '<td>Product Name</td>';
		$result .= '<td>Quantity</td>';
		$result .= '</tr>';
		$result .= '<tr>';
		$result .= '<td>'.$prodCode.'</td>';
		$result .= '<td>'.$quantity.'</td>';
		$result .= '</tr>';
		$result .= '</table>';
	}
	
	return $result;
}

/*
	return true or false
*/
function sendMail($rec, $sender, $sub, $content){	
	$headers = 'From: '. $sender . "\r\n";
  $headers .= 'Reply-To: '. $sender . "\r\n";
	$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
	
	return mail($rec, $sub, $content, $headers);
}

/*
	return true or false
*/
function ccMail($rec, $cc, $sender, $sub, $content){	
	$headers = 'From: '. $sender . "\r\n";
  $headers .= 'Reply-To: '. $sender . "\r\n";
	$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
	$headers .= 'Cc: ' . $cc . "\r\n";
	
	return mail($rec, $sub, $content, $headers);
}

/*
  	retrieving data from 
*/
function getProductListByProductID($pid){
	global $PRODUCTS_ITEMS;

	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$PRODUCTS_ITEMS." where ProductID = ".$pid;
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}
?>